#!/usr/bin/env python
# -*- encoding: utf-8 -*-
import pandas as pd
import datetime

from xpy3lib.utils.XDataFrameUtils import XDataFrameUtils
from xpy3lib.utils import db_utils as util
from xpy3lib.XRetryableQuery import XRetryableQuery
from xpy3lib.XRetryableSave import XRetryableSave

from sicut.AbsDPJob import AbsDPJob
from sicut.service.RequestSiCutAPI import RequestSiCutAPI
from sicut.service.RequestSiCostAPI import RequestSiCostAPI


class CutJob(AbsDPJob):
    data_type = None

    def __init__(self,
                 p_config=None,
                 p_db_conn_mpp=None):
        """

        :param p_config:
        :param p_db_conn_mpp:
        """
        super(CutJob, self).__init__(p_config=p_config,
                                     p_db_conn_mpp=p_db_conn_mpp,
                                     p_db_conn_rds=None,
                                     p_db_conn_dbprod7=None)


    def do_execute(self):
        """
        """
        self.logger.info('CutJob.do_execute')

        now = datetime.datetime.now()
        # now = datetime.datetime(year=2021, month=7, day=6, hour=0, minute=0)
        oneday = datetime.timedelta(days=1)
        prev_day = now - oneday
        prev_day2 = prev_day - oneday
        prev_day3 = prev_day2 - oneday
        prev_day4 = prev_day3 - oneday
        prev_day5 = prev_day4 - oneday
        prev_day6 = prev_day5 - oneday
        prev_day7 = prev_day6 - oneday
        prev_day8 = prev_day7 - oneday

        s = datetime.datetime(year=prev_day8.year, month=prev_day8.month, day=prev_day8.day, hour=20, minute=0,
                              second=0)
        e = datetime.datetime(year=prev_day.year, month=prev_day.month, day=prev_day.day, hour=20, minute=0, second=0)
        max_end_time_1 = s.strftime('%Y%m%d%H%M%S')
        max_end_time_2 = e.strftime('%Y%m%d%H%M%S')
        # max_end_time_1 = '20220926000000'
        # max_end_time_2 = '20220929000000'
        day = prev_day.strftime('%Y%m%d')  # '20201107'
        month = prev_day.strftime('%Y%m')  # '202011'
        sql = " DELETE FROM " \
              " BGTAMSSI00.T_DWD_FACT_ZZQM_TRIM" \
              " WHERE 1=1 " \
              " and PROD_END_TIME>'%s' " \
              " and PROD_END_TIME<='%s'"(max_end_time_1, max_end_time_2)

        self.db_conn_mpp.execute(sql)

        # sql = " SELECT " \
        #       " * from( " \
        #       " select max(a.prod_end_time) as max_end_time, a.in_mat_no_1 " \
        #       " from( " \
        #       " select distinct " \
        #       " mat_no,pre_unit_code,prod_end_time,mat_act_width,mat_act_wt,complex_decide_code,in_mat_no_1 " \
        #       " from MMSI.TMMSI96 " \
        #       " where event_id='42' " \
        #       " )as a " \
        #       " group by a.in_mat_no_1 " \
        #       " )as b" \
        #       " where b.max_end_time>'%s' " \
        #       " and b.max_end_time<='%s'" % (max_end_time_1, max_end_time_2)

        # " where b.max_end_time>'%s' AND b.in_mat_no_1='10847939200' " \
        #20221009新增
        sql = " SELECT " \
              " PROD_END_TIME AS MAX_END_TIME,MAT_NO AS IN_MAT_NO_1,MAT_ACT_WIDTH FROM " \
              " mmsi.tmmsi01 " \
              " where SUBSTR(ST_NO,1,2)='IH' and PRE_UNIT_CODE IN ('Q114', 'Q214', 'Q314', 'Q414') " \
              " and PROD_END_TIME>'%s' " \
              " and PROD_END_TIME<='%s' " \
              " UNION ALL " \
              " SELECT " \
              " PROD_END_TIME AS MAX_END_TIME,MAT_NO AS IN_MAT_NO_1,MAT_ACT_WIDTH FROM " \
              " mmsi.hmmsi01 " \
              " where SUBSTR(ST_NO,1,2)='IH' and PRE_UNIT_CODE IN ('Q114', 'Q214', 'Q314', 'Q414') " \
              " and PROD_END_TIME>'%s' " \
              " and PROD_END_TIME<='%s' " \
              " UNION ALL " \
              " SELECT " \
              " PROD_END_TIME AS MAX_END_TIME,MAT_NO AS IN_MAT_NO_1,MAT_ACT_WIDTH FROM " \
              " mmsi.hmmsi01_HIS " \
              " where SUBSTR(ST_NO,1,2)='IH' and PRE_UNIT_CODE IN ('Q114', 'Q214', 'Q314', 'Q414') " \
              " and PROD_END_TIME>'%s' " \
              " and PROD_END_TIME<='%s'" % (max_end_time_1, max_end_time_2, max_end_time_1, max_end_time_2, max_end_time_1, max_end_time_2)
        self.logger.info(sql)
        df1 = XRetryableQuery(p_db_conn=self.db_conn_mpp, p_sql=sql, p_max_times=5).redo()
        success = df1.empty is False
        if success is True:
            df1.columns = df1.columns.str.upper()
            self.logger.info(df1)

        print(df1)
        def __cal_end_time2(x):
            # NOTE 距离最近的08:00或20:00
            t = datetime.datetime.strptime(str(x.MAX_END_TIME), '%Y%m%d%H%M%S')
            prev_day = t - datetime.timedelta(days=1)

            today_20 = datetime.datetime(year=t.year, month=t.month, day=t.day, hour=20).strftime('%Y%m%d%H%M%S')
            today_8 = datetime.datetime(year=t.year, month=t.month, day=t.day, hour=8).strftime('%Y%m%d%H%M%S')
            yestoday_20 = datetime.datetime(year=prev_day.year, month=prev_day.month, day=prev_day.day,
                                            hour=20).strftime('%Y%m%d%H%M%S')
            if x.MAX_END_TIME >= today_20:
                rst = today_20
            elif x.MAX_END_TIME >= today_8:
                rst = today_8
            else:
                rst = yestoday_20
            return rst

        df1['FROM'] = df1.apply(lambda x: __cal_end_time2(x), axis=1)
        sql = "select FROM,DATE as PROD_DATE,SHIFT as PROD_SHIFT,TURN as PROD_TURN from BGTAMAL1.BASE_SU_J003"
        dataframe_BASE_SU_J003 = XRetryableQuery(p_db_conn=self.db_conn_mpp, p_sql=sql, p_max_times=5).redo()
        dataframe_BASE_SU_J003.columns = dataframe_BASE_SU_J003.columns.str.upper()
        df1.columns = df1.columns.str.upper()
        df1_new = pd.merge(df1, dataframe_BASE_SU_J003, on=['FROM'], how='left')
        self.logger.info(df1_new)

        def __cal_prod_turn(x):
            rst = int(x.PROD_TURN)
            rst = str(rst)
            return rst

        df1_new['PROD_TURN'] = df1_new.apply(lambda x: __cal_prod_turn(x), axis=1)

        def __cal_prod_month(x):
            t = datetime.datetime.strptime(str(x.PROD_DATE), '%Y%m%d')
            return t.strftime('%Y%m')

        df1_new['PROD_MONTH'] = df1_new.apply(lambda x: __cal_prod_month(x), axis=1)
        # df1_new['MAT_ACT_WIDTH'] = None
        print(df1_new)
        print('kaishixunhuan')
        vvv = 0
        for index, row in df1_new.iterrows():
            w = row['IN_MAT_NO_1']
            width = row['MAT_ACT_WIDTH']
            # 母卷的所有子卷号
            success, target_mat_act_width, target_sub_out_mat_no = RequestSiCutAPI(p_mat_no=row['IN_MAT_NO_1']).request()
            # target_mat_act_width = 1092
            # df1_new.loc[index, 'MAT_ACT_WIDTH'] = round(target_mat_act_width, 2)

            df2 = pd.DataFrame(data=[], columns=RequestSiCostAPI.FIXED_COLUMNS)
            # df2 = pd.DataFrame(data=[], columns=['MAT_NO', 'MAT_ACT_WIDTH', 'MAT_ACT_WT', 'COMPLEX_DECIDE_CODE', 'PRE_UNIT_CODE'])
            print(df2)
            for sub_out_mat_no in target_sub_out_mat_no:
                success, tmp_df = RequestSiCostAPI(p_mat_no=sub_out_mat_no).request()
                # sql = " SELECT " \
                #       " MAT_NO,MAT_ACT_WIDTH,MAT_ACT_WT,COMPLEX_DECIDE_CODE,ATTRI_UNIT as PRE_UNIT_CODE " \
                #       " FROM mmsi.tmmsi16 " \
                #       " where  " \
                #       " MAT_NO ='%s'" % (sub_out_mat_no)
                # self.logger.info(sql)
                # tmp_df = XRetryableQuery(p_db_conn=self.db_conn_mpp, p_sql=sql, p_max_times=5).redo()
                # success = tmp_df.empty is False
                # if success is True:
                #     tmp_df.columns = tmp_df.columns.str.upper()
                #     print(tmp_df)
                #     df2 = pd.merge(df2, tmp_df, on=['MAT_NO', 'MAT_ACT_WIDTH', 'MAT_ACT_WT', 'COMPLEX_DECIDE_CODE', 'PRE_UNIT_CODE'], how='outer')



                if success:
                    df2 = pd.merge(df2, tmp_df, on=RequestSiCostAPI.FIXED_COLUMNS, how='outer')

            # vvv += 1
            # aa = 'df2__%s.xls' % (str(vvv))
            # XDataFrameUtils.dataframe2excel(p_dataframe=df2, p_file_name=aa)

            if df2.empty:
                continue

            # 这个sql的意义就是取出这个母卷的所有子卷的有用信息，现在出了点问题  要用接口去读
            # 相当于对那5个子卷号去循环，调用接口
            # 每次只有一行数据，然后循环完是五行，有用的就几列  别的留着也行  然后合并成一个df
            # 就是相当于代替了那个sql
            # 而且接口就是之前那个sicost的接口  接口名和传入的json一模一样
            # 然后读出来之后可以不用定位到具体行
            # 各级300多列
            # 也不用定位到列  直接就全取了  弄到df里
            # sql = " select distinct " \
            #       " mat_no,pre_unit_code,prod_end_time,mat_act_width,mat_act_wt,complex_decide_code,in_mat_no_1 " \
            #       " from MMSI.TMMSI96 " \
            #       " where event_id='42' " \
            #       " and in_mat_no_1='%s'" % (w)
            # self.logger.info(sql)
            # df22 = XRetryableQuery(p_db_conn=self.db_conn_mpp, p_sql=sql, p_max_times=5).redo()
            # self.logger.info(df22)

            def __cal_CUT(x):
                rst = 0
                if width - x.MAT_ACT_WIDTH < 10:
                    rst = 0
                else:
                    rst = width - x.MAT_ACT_WIDTH
                return rst

            df2['CUT'] = df2.apply(lambda x: __cal_CUT(x), axis=1)

            def __cal_CUT_WT(x):
                rst = 0
                if width - x.MAT_ACT_WIDTH < 10:
                    rst = 0
                else:
                    rst = x.MAT_ACT_WT
                return rst

            df2['CUT_WT'] = df2.apply(lambda x: __cal_CUT_WT(x), axis=1)
            df2['CUT_WT_CUT'] = df2['CUT_WT'] * df2['CUT']
            df2.columns = df2.columns.str.upper()

            m = df2.loc[0, 'PRE_UNIT_CODE']
            df1_new.loc[index, 'UNIT_CODE'] = m
            self.logger.info(m)

            self.logger.info(df2)
            a = df2['CUT_WT_CUT'].sum()
            b = df2['CUT_WT'].sum()
            c = a / b
            df1_new.loc[index, 'TRIM_ACT_WT'] = round(b, 2)
            df1_new.loc[index, 'ACT_TRIMMING'] = round(c, 2)
            self.logger.info(a)
            self.logger.info(b)
            self.logger.info(c)
            sql = " select mat_no,'mat_len_1' as mat_len_no,a.mat_len_1 as mat_len,'mat_width_1' as mat_width_no,a.mat_width_1 as mat_width,'ws_side_cut_len_1' as ws_side_cut_len_no,a.ws_side_cut_len_1 as ws_side_cut_len,'ds_side_cut_len_1' as ds_side_cut_len_no,a.ds_side_cut_len_1 as ds_side_cut_len " \
                  " from (select mat_no,prod_shift_no,prod_group_no,prod_end_time,mat_len_1,mat_width_1,ws_side_cut_len_1,ds_side_cut_len_1,mat_len_2,mat_width_2,ws_side_cut_len_2,ds_side_cut_len_2,mat_len_3,mat_width_3,ws_side_cut_len_3,ds_side_cut_len_3,mat_len_4,mat_width_4,ws_side_cut_len_4,ds_side_cut_len_4,mat_len_5,mat_width_5,ws_side_cut_len_5,ds_side_cut_len_5,mat_len_6,mat_width_6,ws_side_cut_len_6,ds_side_cut_len_6,mat_len_7,mat_width_7,ws_side_cut_len_7,ds_side_cut_len_7,mat_len_8,mat_width_8,ws_side_cut_len_8,ds_side_cut_len_8 from MMSI.TMMSIK108 " \
                  " where mat_no='%s' " \
                  " ) a UNION ALL " \
                  " select mat_no,'mat_len_2' as mat_len_no,a.mat_len_2 as mat_len,'mat_width_2' as mat_width_no,a.mat_width_2 as mat_width,'ws_side_cut_len_2' as ws_side_cut_len_no,a.ws_side_cut_len_2 as ws_side_cut_len,'ds_side_cut_len_2' as ds_side_cut_len_no,a.ds_side_cut_len_2 as ds_side_cut_len " \
                  " from (select mat_no,prod_shift_no,prod_group_no,prod_end_time,mat_len_1,mat_width_1,ws_side_cut_len_1,ds_side_cut_len_1,mat_len_2,mat_width_2,ws_side_cut_len_2,ds_side_cut_len_2,mat_len_3,mat_width_3,ws_side_cut_len_3,ds_side_cut_len_3,mat_len_4,mat_width_4,ws_side_cut_len_4,ds_side_cut_len_4,mat_len_5,mat_width_5,ws_side_cut_len_5,ds_side_cut_len_5,mat_len_6,mat_width_6,ws_side_cut_len_6,ds_side_cut_len_6,mat_len_7,mat_width_7,ws_side_cut_len_7,ds_side_cut_len_7,mat_len_8,mat_width_8,ws_side_cut_len_8,ds_side_cut_len_8 from MMSI.TMMSIK108 " \
                  " where mat_no='%s' " \
                  " ) a UNION ALL " \
                  " select mat_no,'mat_len_3' as mat_len_no,a.mat_len_3 as mat_len,'mat_width_3' as mat_width_no,a.mat_width_3 as mat_width,'ws_side_cut_len_3' as ws_side_cut_len_no,a.ws_side_cut_len_3 as ws_side_cut_len,'ds_side_cut_len_3' as ds_side_cut_len_no,a.ds_side_cut_len_3 as ds_side_cut_len " \
                  " from (select mat_no,prod_shift_no,prod_group_no,prod_end_time,mat_len_1,mat_width_1,ws_side_cut_len_1,ds_side_cut_len_1,mat_len_2,mat_width_2,ws_side_cut_len_2,ds_side_cut_len_2,mat_len_3,mat_width_3,ws_side_cut_len_3,ds_side_cut_len_3,mat_len_4,mat_width_4,ws_side_cut_len_4,ds_side_cut_len_4,mat_len_5,mat_width_5,ws_side_cut_len_5,ds_side_cut_len_5,mat_len_6,mat_width_6,ws_side_cut_len_6,ds_side_cut_len_6,mat_len_7,mat_width_7,ws_side_cut_len_7,ds_side_cut_len_7,mat_len_8,mat_width_8,ws_side_cut_len_8,ds_side_cut_len_8 from MMSI.TMMSIK108 " \
                  " where mat_no='%s' " \
                  " ) a UNION ALL " \
                  " select mat_no,'mat_len_4' as mat_len_no,a.mat_len_4 as mat_len,'mat_width_4' as mat_width_no,a.mat_width_4 as mat_width,'ws_side_cut_len_4' as ws_side_cut_len_no,a.ws_side_cut_len_4 as ws_side_cut_len,'ds_side_cut_len_4' as ds_side_cut_len_no,a.ds_side_cut_len_4 as ds_side_cut_len " \
                  " from (select mat_no,prod_shift_no,prod_group_no,prod_end_time,mat_len_1,mat_width_1,ws_side_cut_len_1,ds_side_cut_len_1,mat_len_2,mat_width_2,ws_side_cut_len_2,ds_side_cut_len_2,mat_len_3,mat_width_3,ws_side_cut_len_3,ds_side_cut_len_3,mat_len_4,mat_width_4,ws_side_cut_len_4,ds_side_cut_len_4,mat_len_5,mat_width_5,ws_side_cut_len_5,ds_side_cut_len_5,mat_len_6,mat_width_6,ws_side_cut_len_6,ds_side_cut_len_6,mat_len_7,mat_width_7,ws_side_cut_len_7,ds_side_cut_len_7,mat_len_8,mat_width_8,ws_side_cut_len_8,ds_side_cut_len_8 from MMSI.TMMSIK108 " \
                  " where mat_no='%s' " \
                  " ) a UNION ALL " \
                  " select mat_no,'mat_len_5' as mat_len_no,a.mat_len_5 as mat_len,'mat_width_5' as mat_width_no,a.mat_width_5 as mat_width,'ws_side_cut_len_5' as ws_side_cut_len_no,a.ws_side_cut_len_5 as ws_side_cut_len,'ds_side_cut_len_5' as ds_side_cut_len_no,a.ds_side_cut_len_5 as ds_side_cut_len " \
                  " from (select mat_no,prod_shift_no,prod_group_no,prod_end_time,mat_len_1,mat_width_1,ws_side_cut_len_1,ds_side_cut_len_1,mat_len_2,mat_width_2,ws_side_cut_len_2,ds_side_cut_len_2,mat_len_3,mat_width_3,ws_side_cut_len_3,ds_side_cut_len_3,mat_len_4,mat_width_4,ws_side_cut_len_4,ds_side_cut_len_4,mat_len_5,mat_width_5,ws_side_cut_len_5,ds_side_cut_len_5,mat_len_6,mat_width_6,ws_side_cut_len_6,ds_side_cut_len_6,mat_len_7,mat_width_7,ws_side_cut_len_7,ds_side_cut_len_7,mat_len_8,mat_width_8,ws_side_cut_len_8,ds_side_cut_len_8 from MMSI.TMMSIK108 " \
                  " where mat_no='%s' " \
                  " ) a UNION ALL " \
                  " select mat_no,'mat_len_6' as mat_len_no,a.mat_len_6 as mat_len,'mat_width_6' as mat_width_no,a.mat_width_6 as mat_width,'ws_side_cut_len_6' as ws_side_cut_len_no,a.ws_side_cut_len_6 as ws_side_cut_len,'ds_side_cut_len_6' as ds_side_cut_len_no,a.ds_side_cut_len_6 as ds_side_cut_len " \
                  " from (select mat_no,prod_shift_no,prod_group_no,prod_end_time,mat_len_1,mat_width_1,ws_side_cut_len_1,ds_side_cut_len_1,mat_len_2,mat_width_2,ws_side_cut_len_2,ds_side_cut_len_2,mat_len_3,mat_width_3,ws_side_cut_len_3,ds_side_cut_len_3,mat_len_4,mat_width_4,ws_side_cut_len_4,ds_side_cut_len_4,mat_len_5,mat_width_5,ws_side_cut_len_5,ds_side_cut_len_5,mat_len_6,mat_width_6,ws_side_cut_len_6,ds_side_cut_len_6,mat_len_7,mat_width_7,ws_side_cut_len_7,ds_side_cut_len_7,mat_len_8,mat_width_8,ws_side_cut_len_8,ds_side_cut_len_8 from MMSI.TMMSIK108 " \
                  " where mat_no='%s' " \
                  " ) a UNION ALL " \
                  " select mat_no,'mat_len_7' as mat_len_no,a.mat_len_7 as mat_len,'mat_width_7' as mat_width_no,a.mat_width_7 as mat_width,'ws_side_cut_len_7' as ws_side_cut_len_no,a.ws_side_cut_len_7 as ws_side_cut_len,'ds_side_cut_len_7' as ds_side_cut_len_no,a.ds_side_cut_len_7 as ds_side_cut_len " \
                  " from (select mat_no,prod_shift_no,prod_group_no,prod_end_time,mat_len_1,mat_width_1,ws_side_cut_len_1,ds_side_cut_len_1,mat_len_2,mat_width_2,ws_side_cut_len_2,ds_side_cut_len_2,mat_len_3,mat_width_3,ws_side_cut_len_3,ds_side_cut_len_3,mat_len_4,mat_width_4,ws_side_cut_len_4,ds_side_cut_len_4,mat_len_5,mat_width_5,ws_side_cut_len_5,ds_side_cut_len_5,mat_len_6,mat_width_6,ws_side_cut_len_6,ds_side_cut_len_6,mat_len_7,mat_width_7,ws_side_cut_len_7,ds_side_cut_len_7,mat_len_8,mat_width_8,ws_side_cut_len_8,ds_side_cut_len_8 from MMSI.TMMSIK108 " \
                  " where mat_no='%s' " \
                  " ) a UNION ALL " \
                  " select mat_no,'mat_len_8' as mat_len_no,a.mat_len_8 as mat_len,'mat_width_8' as mat_width_no,a.mat_width_8 as mat_width,'ws_side_cut_len_8' as ws_side_cut_len_no,a.ws_side_cut_len_8 as ws_side_cut_len,'ds_side_cut_len_8' as ds_side_cut_len_no,a.ds_side_cut_len_8 as ds_side_cut_len " \
                  " from (select mat_no,prod_shift_no,prod_group_no,prod_end_time,mat_len_1,mat_width_1,ws_side_cut_len_1,ds_side_cut_len_1,mat_len_2,mat_width_2,ws_side_cut_len_2,ds_side_cut_len_2,mat_len_3,mat_width_3,ws_side_cut_len_3,ds_side_cut_len_3,mat_len_4,mat_width_4,ws_side_cut_len_4,ds_side_cut_len_4,mat_len_5,mat_width_5,ws_side_cut_len_5,ds_side_cut_len_5,mat_len_6,mat_width_6,ws_side_cut_len_6,ds_side_cut_len_6,mat_len_7,mat_width_7,ws_side_cut_len_7,ds_side_cut_len_7,mat_len_8,mat_width_8,ws_side_cut_len_8,ds_side_cut_len_8 from MMSI.TMMSIK108 " \
                  " where mat_no='%s' " \
                  " ) a  " \
                  " order by mat_len_no " % (w, w, w, w, w, w, w, w)
            self.logger.info(sql)
            df3 = XRetryableQuery(p_db_conn=self.db_conn_mpp, p_sql=sql, p_max_times=5).redo()
            if df3.empty:
                continue
            else:
                df3.columns = df3.columns.str.upper()
                df3['counter'] = range(len(df3))

                self.logger.info(df3)
                df4 = df3.shift(-1)

                df4.drop(['MAT_NO'], axis=1, inplace=True)
                df4.drop(['MAT_LEN_NO'], axis=1, inplace=True)
                df4.drop(['MAT_WIDTH_NO'], axis=1, inplace=True)
                df4.drop(['MAT_WIDTH'], axis=1, inplace=True)
                df4.drop(['WS_SIDE_CUT_LEN_NO'], axis=1, inplace=True)
                df4.drop(['WS_SIDE_CUT_LEN'], axis=1, inplace=True)
                df4.drop(['DS_SIDE_CUT_LEN_NO'], axis=1, inplace=True)
                df4.drop(['DS_SIDE_CUT_LEN'], axis=1, inplace=True)
                df4.rename(columns={'MAT_LEN': 'NEXT_MAT_LEN'}, inplace=True)
                df4['counter'] = range(len(df4))
                self.logger.info(df4)
                df5 = pd.merge(df3, df4, on=['counter'], how='left')
                df5 = df5.fillna(value=0)
                self.logger.info(df5)

                def __cal_CUT_LEN(x):
                    rst = 0
                    if width - x.MAT_WIDTH <= 23 or width - x.MAT_WIDTH == width:
                        rst = 0
                    else:
                        rst = x.MAT_LEN - x.NEXT_MAT_LEN
                    return rst

                df5['CUT_LEN'] = df5.apply(lambda x: __cal_CUT_LEN(x), axis=1)

                def __cal_CUT_PR(x):
                    rst = 0
                    if width - x.MAT_WIDTH <= 23 or width - x.MAT_WIDTH == width:
                        rst = 0
                    else:
                        rst = width - x.MAT_WIDTH
                    return rst

                df5['CUT_PR'] = df5.apply(lambda x: __cal_CUT_PR(x), axis=1)
                df5['CUT_LEN_CUT_PR'] = df5['CUT_LEN'] * df5['CUT_PR']
                df5['WS_LEN_CUT_PR'] = df5['WS_SIDE_CUT_LEN'] * df5['CUT_LEN']
                df5['DS_LEN_CUT_PR'] = df5['DS_SIDE_CUT_LEN'] * df5['CUT_LEN']

                x = df5['CUT_LEN_CUT_PR'].sum()
                y = df5['CUT_LEN'].sum()
                x_ws = df5['WS_LEN_CUT_PR'].sum()
                x_ds = df5['DS_LEN_CUT_PR'].sum()
                z = x / y
                z_ws = x_ws / y
                z_ds = x_ds / y
                self.logger.info(x)
                self.logger.info(y)
                self.logger.info(z)
                self.logger.info(z_ws)
                self.logger.info(z_ds)

                # NOTE 逐行添加新属性, 注意新属性名必须和表的列名对应上，否则无法入库
                df1_new.loc[index, 'TRIM_PREDICT_LEN'] = round(y, 2)
                df1_new.loc[index, 'PREDICT_TRIMMING'] = round(z, 2)
                df1_new.loc[index, 'PREDICT_WS_TRIMMING'] = round(z_ws, 2)
                df1_new.loc[index, 'PREDICT_DS_TRIMMING'] = round(z_ds, 2)

        # NOTE 删除掉df1_new不需要的属性
        # drop ....
        df1_new.columns = df1_new.columns.str.upper()
        df1_new.rename(columns={'MAX_END_TIME': 'END_TIME'}, inplace=True)
        df1_new.rename(columns={'IN_MAT_NO_1': 'MAT_NO'}, inplace=True)
        df1_new.drop(['FROM'], axis=1, inplace=True)
        df1_new.drop(['PROD_SHIFT'], axis=1, inplace=True)

        def __cal_REC_CREATOR(x):
            t = '----'
            return t

        df1_new['REC_CREATOR'] = df1_new.apply(lambda x: __cal_REC_CREATOR(x), axis=1)

        def __cal_REC_CREATE_TIME(x):
            t = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
            return t

        df1_new['REC_CREATE_TIME'] = df1_new.apply(lambda x: __cal_REC_CREATE_TIME(x), axis=1)

        def __cal_REC_REVISOR(x):
            t = '----'
            return t

        df1_new['REC_REVISOR'] = df1_new.apply(lambda x: __cal_REC_REVISOR(x), axis=1)

        def __cal_REC_REVISOR_TIME(x):
            t = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
            return t

        df1_new['REC_REVISOR_TIME'] = df1_new.apply(lambda x: __cal_REC_REVISOR_TIME(x), axis=1)
        print(df1_new)
        self.logger.info(df1_new)
        #XDataFrameUtils.dataframe2excel(p_dataframe=df1_new, p_file_name="df1_new.xls")

        # NOTE 写库, 注意填写为正确的connection tablename schema
        XRetryableSave(p_db_conn=self.db_conn_mpp, p_table_name='T_DWD_FACT_ZZQM_TRIM', p_schema='BGTAMSSI00',
                       p_dataframe=df1_new,
                       p_max_times=5).redo()

        # NOTE 机组汇总
        self.__execute_ji_zhu_hui_zong(p_db_conn_mpp=self.db_conn_mpp,
                                       p_table_name='T_DWD_FACT_ZZQM_DIFFTRIM',
                                       p_type='PROD_DATE',
                                       p_value=day,
                                       p_statistics_type='d')

        self.__execute_ji_zhu_hui_zong(p_db_conn_mpp=self.db_conn_mpp,
                                       p_table_name='T_DWD_FACT_ZZQM_DIFFTRIM',
                                       p_type='PROD_MONTH',
                                       p_value=month,
                                       p_day=day,
                                       p_statistics_type='m')

        self.__step_0()
        self.__step_1()

    def __step_0(self):
        pass

    def __step_1(self):
        pass

    def __execute_ji_zhu_hui_zong(self,
                                  p_db_conn_mpp=None,
                                  p_table_name=None,
                                  p_type=None,
                                  p_value=None,
                                  p_day=None,
                                  p_statistics_type=None):
        c = " and a.PROD_DATE = '%s' " % (p_value)
        if p_day is not None:
            c = " and a.PROD_MONTH = '%s' and a.PROD_DATE<= '%s' " % (p_value, p_day)

        # NOTE  FACTORY_DESC,PROD_TURN
        sql = "select " \
              "a.UNIT_CODE,a.PROD_TURN as STATISTICS_TURN, " \
              "AVG(a.ACT_TRIMMING) as ACT_TRIMMING, " \
              "AVG(a.PREDICT_TRIMMING) as PREDICT_TRIMMING, " \
              "AVG(a.ACT_TRIMMING)-AVG(a.PREDICT_TRIMMING) as DIFF_TRIMMING, " \
              "'%s' as STATISTICS_TYPE, " \
              "'%s' as STATISTICS_TIME " \
              "from " \
              "(SELECT MAT_NO,UNIT_CODE,ACT_TRIMMING,PREDICT_TRIMMING,ACT_TRIMMING-PREDICT_TRIMMING as DIFF_TRIMMING,PROD_DATE,PROD_MONTH,PROD_TURN FROM BGTAMSSI00.T_DWD_FACT_ZZQM_TRIM)AS a " \
              "WHERE a.ACT_TRIMMING is not null " \
              "and a.PREDICT_TRIMMING is not null " \
              " %s " \
              "group by " \
              "a.UNIT_CODE, " \
              "a.PROD_TURN" % (p_statistics_type, p_value, c)
        self.logger.info(sql)
        df6 = XRetryableQuery(p_db_conn=p_db_conn_mpp, p_sql=sql, p_max_times=5).redo()
        df6.columns = df6.columns.str.upper()

        def __cal_REC_CREATOR(x):
            t = '----'
            return t

        df6['REC_CREATOR'] = df6.apply(lambda x: __cal_REC_CREATOR(x), axis=1)

        def __cal_REC_CREATE_TIME(x):
            t = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
            return t

        df6['REC_CREATE_TIME'] = df6.apply(lambda x: __cal_REC_CREATE_TIME(x), axis=1)

        def __cal_REC_REVISOR(x):
            t = '----'
            return t

        df6['REC_REVISOR'] = df6.apply(lambda x: __cal_REC_REVISOR(x), axis=1)

        def __cal_REC_REVISOR_TIME(x):
            t = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
            return t

        df6['REC_REVISOR_TIME'] = df6.apply(lambda x: __cal_REC_REVISOR_TIME(x), axis=1)
        XRetryableSave(p_db_conn=p_db_conn_mpp, p_table_name='T_DWD_FACT_ZZQM_DIFFTRIM', p_schema='BGTAMSSI00',
                       p_dataframe=df6,
                       p_max_times=5).redo()

        sql = "select " \
              "a.UNIT_CODE,'9' as STATISTICS_TURN, " \
              "AVG(a.ACT_TRIMMING) as ACT_TRIMMING, " \
              "AVG(a.PREDICT_TRIMMING) as PREDICT_TRIMMING, " \
              "AVG(a.ACT_TRIMMING)-AVG(a.PREDICT_TRIMMING) as DIFF_TRIMMING, " \
              "'%s' as STATISTICS_TYPE, " \
              "'%s' as STATISTICS_TIME " \
              "from " \
              "(SELECT MAT_NO,UNIT_CODE,ACT_TRIMMING,PREDICT_TRIMMING,ACT_TRIMMING-PREDICT_TRIMMING as DIFF_TRIMMING,PROD_DATE,PROD_MONTH,PROD_TURN FROM BGTAMSSI00.T_DWD_FACT_ZZQM_TRIM)AS a " \
              "WHERE a.ACT_TRIMMING is not null " \
              "and a.PREDICT_TRIMMING is not null " \
              " %s " \
              "group by " \
              "a.UNIT_CODE" % (p_statistics_type, p_value, c)
        self.logger.info(sql)
        df7 = XRetryableQuery(p_db_conn=p_db_conn_mpp, p_sql=sql, p_max_times=5).redo()
        df7.columns = df7.columns.str.upper()

        def __cal_REC_CREATOR(x):
            t = '----'
            return t

        df7['REC_CREATOR'] = df7.apply(lambda x: __cal_REC_CREATOR(x), axis=1)

        def __cal_REC_CREATE_TIME(x):
            t = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
            return t

        df7['REC_CREATE_TIME'] = df7.apply(lambda x: __cal_REC_CREATE_TIME(x), axis=1)

        def __cal_REC_REVISOR(x):
            t = '----'
            return t

        df7['REC_REVISOR'] = df7.apply(lambda x: __cal_REC_REVISOR(x), axis=1)

        def __cal_REC_REVISOR_TIME(x):
            t = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
            return t

        df7['REC_REVISOR_TIME'] = df7.apply(lambda x: __cal_REC_REVISOR_TIME(x), axis=1)
        XRetryableSave(p_db_conn=p_db_conn_mpp, p_table_name='T_DWD_FACT_ZZQM_DIFFTRIM', p_schema='BGTAMSSI00',
                       p_dataframe=df7,
                       p_max_times=5).redo()
